﻿using Lufei_CaterModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lufei_CaterDAL
{
    public class DeskInfoDAL
    {


        /// <summary>
        /// 查询所有没有被删除的房间信息  RoomID
        /// </summary>
        /// <param name="rooID"></param>
        /// <returns></returns>
        public List<DeskInfo> GetDesksByRoomID(int roomID)
        {
            string sql = "select * from deskinfo where delflag=0 and roomid ="+ roomID;

           DataTable dt= SqlHelper.ExecuteDataTable(sql);
            List<DeskInfo> list = new List<DeskInfo>(); 
            if (dt.Rows.Count>0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(RowToDeskInfo(dr));
                }
            }
            return list;
        }


        /// <summary>
        /// 查询所有没有被删除的桌子
        /// </summary>
        /// <param name="rooID"></param>
        /// <returns></returns>
        public List<DeskInfo> GetDesks()
        {
            string sql = "select * from deskinfo where delflag=0 ";

            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            List<DeskInfo> list = new List<DeskInfo>();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(RowToDeskInfo(dr));
                }
            }
            return list;
        }



        /// <summary>
        /// 关系转模型
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private DeskInfo RowToDeskInfo(DataRow dr)
        {
            DeskInfo dk = new DeskInfo();
            //dk.DelFlag=
            dk.DeskId = Convert.ToInt32(dr["DeskId"]);
            dk.DeskName = dr["DeskName"].ToString();
            dk.DeskRegion = dr["DeskRegion"].ToString();
            dk.DeskRemark = dr["DeskRemark"].ToString();
            dk.DeskState = Convert.ToInt32(dr["DeskState"]);
            // dk.DeskStateString = dr["DeskStateString"].ToString();
            dk.RoomId = Convert.ToInt32(dr["RoomId"]);
            dk.SubBy = Convert.ToInt32(dr["SubBy"]);
            dk.SubTime = Convert.ToDateTime(dr["SubTime"]);
            return dk;
        }


        /// <summary>
        /// 根据餐桌id修改餐桌的属性
        /// </summary>
        /// <param name="deskstate"></param>
        /// <param name="deskid"></param>
        /// <returns></returns>
        public int UpdateDeskStateByID(int deskstate, int deskid)
        {
            string sql = "update deskinfo set deskstate = @deskstate where deskid=@deskid";

            SQLiteParameter[] param = new SQLiteParameter[]
            {
                new SQLiteParameter("@deskstate",deskstate),
                new SQLiteParameter("@deskid",deskid)
            };

           return SqlHelper.ExecuteNonQuery(sql,param);
         }

 
        /// <summary>
        /// 根据id删除数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteDeskByID(int id)
        {
            string sql = "update deskinfo set DelFlag=1 where DeskId=" + id;

            return  SqlHelper.ExecuteNonQuery(sql);
        }


        /// <summary>
        /// 添加桌子
        /// </summary>
        /// <param name="di"></param>
        /// <returns></returns>
        public int InsertDesk(DeskInfo ri)
        {
            string sql = "insert into deskinfo (RoomId,DeskName,DeskRegion,DeskState,DelFlag,SubTime,SubBy)values(@RoomId,@DeskName,@DeskRegion,@DeskState,@DelFlag,@SubTime,@SubBy)";
            SQLiteParameter[] param = new SQLiteParameter[] {
                new SQLiteParameter("@RoomId",ri.RoomId),
                new SQLiteParameter("@DeskName",ri.DeskName),
                new SQLiteParameter("@DeskRegion",ri.DeskRegion),
                new SQLiteParameter("@DeskState",ri.DeskState),
                new SQLiteParameter("@DelFlag",ri.DelFlag),
                new SQLiteParameter("@SubTime",ri.SubTime),
                new SQLiteParameter("@SubBy",ri.SubBy)
            };

          return  SqlHelper.ExecuteNonQuery(sql,param);
        }

        /// <summary>
        /// 根据id查询桌子
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DeskInfo GetDeskInfoByID(int id)
        {
            string sql = "select * from deskinfo where DeskId=" + id;
            DeskInfo deskInfo = new DeskInfo();
            DataTable dt= SqlHelper.ExecuteDataTable(sql);
            foreach (DataRow dr in dt.Rows)
            {
                deskInfo = RowToDeskInfo(dr);
            }
            return deskInfo;
        }


        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="ri"></param>
        /// <returns></returns>
        public int UpdateDeskInfo(DeskInfo ri)
        {
            string sql = "update deskinfo set RoomId=@RoomId,DeskName=@DeskName,DeskRegion=@DeskRegion,DeskRemark=@DeskRemark,DeskState=@DeskState where DeskId=@DeskId;";

            SQLiteParameter[] param = new SQLiteParameter[] {
                new SQLiteParameter("@RoomId",ri.RoomId),
                new SQLiteParameter("@DeskName",ri.DeskName),
                new SQLiteParameter("@DeskRegion",ri.DeskRegion),
                new SQLiteParameter("@DeskRemark",ri.DeskRemark),
                  new SQLiteParameter("@DeskState",ri.DeskState),
                new SQLiteParameter("@DeskId",ri.DeskId),
            };

           return SqlHelper.ExecuteNonQuery(sql,param);
        }
    }

}

